/*
    NOTE:

    This stored procedure handles which dues products appear for each member type during the membership
    signup process.  Dues products include the following in iMIS:

    DUES (normal dues)
    CHAPT (chapter dues)
    SEC (section dues)
    VOL (voluntary dues)
    SUB (subscription dues)

    The User ID is passed into this stored proc should there be a need to implement some business logic
    based on the user's information.
*/
CREATE PROCEDURE [dbo].[BAEImisSubscriptionsGetSubscriptionByMemberType]
    @UserID            AS    varchar(10),
    @MemberType     AS    varchar(5)
    --@ProductType     AS    varchar(31)
AS
    SELECT prod.*
    FROM Member_Types AS mt, Product AS prod
    WHERE mt.MEMBER_TYPE = @MemberType AND
        prod.PROD_TYPE IN ('DUES', 'CHAPT', 'SEC', 'VOL', 'SUB') AND
        (prod.PRODUCT_CODE IN(mt.DUES_CODE_1, mt.DUES_CODE_2, mt.DUES_CODE_3, mt.DUES_CODE_4, mt.DUES_CODE_5,
        mt.DUES_CODE_6, mt.DUES_CODE_7, mt.DUES_CODE_8, mt.DUES_CODE_9, mt.DUES_CODE_10) OR prod.PROD_TYPE = 'SUB')
    AND prod.PRODUCT_CODE NOT IN (select sub.PRODUCT_CODE from Subscriptions as sub where prod.PRODUCT_CODE = sub.PRODUCT_CODE AND ID = @UserID);

GO